﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Npgsql;
using SpectationClient.DataBaseDescription;
//using SpectationClient.DataBaseDescription;

namespace SpectationClient.SQLCommandBuilder {
    public class CommandHelper {
        /*
        public static Object getReturningScalar(NpgsqlCommand cmdTableOIDs) {
            return CommandHelper.getReturningScalar(cmdTableOIDs, null, null);
        }
        */
        public static Object getReturningScalar(NpgsqlCommand cmd, NpgsqlConnection connection, NpgsqlTransaction transaction) {
            if(cmd == null || String.IsNullOrWhiteSpace(cmd.CommandText)) return null;
            if(connection != null) cmd.Connection = connection;
            if(transaction != null) cmd.Transaction = transaction;
            
            return cmd.ExecuteScalar();
        }

        public static DataTable getReturningTable(NpgsqlCommand cmd, NpgsqlConnection connection,  NpgsqlTransaction transaction) {
            if(cmd == null || String.IsNullOrWhiteSpace(cmd.CommandText)) return null;
            if(connection != null) cmd.Connection = connection;
            if(transaction != null) cmd.Transaction = transaction;
            
            DataTable dt =new DataTable();
            NpgsqlDataAdapter da = new NpgsqlDataAdapter();
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }

        public static InsertValueCollection getBridgeTableRowValueCollection(TableInfo ti, DataTable idsLeft, Object idRight, String finalLeft, String finalRight) {
            InsertValueCollection rvc = new InsertValueCollection(ref ti);
            foreach(DataRow r in idsLeft.Rows) {
                InsertValues rv = new InsertValues(ref ti);
                rv.Add(finalLeft, r[0]);
                rv.Add(finalRight, idRight);
                rvc.Add(rv);
            }
            return rvc;
        
        }

        public static InsertValueCollection getBridgeTableRowValueCollection(TableInfo ti, DataTable idsLeft, DataTable idsRight, String finalLeft, String finalRight) {
            InsertValueCollection rvc = new InsertValueCollection(ref ti);
            foreach(DataRow rLeft in idsLeft.Rows) {
                foreach(DataRow rRight in idsRight.Rows) {
                    InsertValues rv = new InsertValues(ref ti);
                    rv.Add(finalLeft, rLeft[0]);
                    rv.Add(finalRight, rRight[0]);
                    rvc.Add(rv);
                }
                
            }
            return rvc;

        }
    }
}
